import java.sql.*;
import java.util.ArrayList;

/**
 * 数据库访问与操作类
 */
public class DatabaseHelper {

    // 数据库 URL，用户名与密码
    static final String DB_URL = "jdbc:mysql://localhost:3306/BaiduNews?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai";
    static final String USERNAME = "root";
    static final String PASSWORD = "123456";

    /**
     * 建立一个到数据库的连接
     *
     * @return 与数据库连接的 {@code Connection} 对象
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(DB_URL, USERNAME, PASSWORD);
    }

    /**
     * 把新闻插入数据库中
     *
     * @param newsList 从 {@link NewsInfoGetter#getNews()} 获得的新闻信息
     */
    public static void insertNewsInfo(ArrayList<NewsInfoGetter.News> newsList) throws Exception {
        try (Connection connection = getConnection()) {
            PreparedStatement preStatement = connection.prepareStatement(
                    "INSERT IGNORE INTO news_info ( title, category, collect_time )" +
                            " VALUES ( ?, ?, ? )",
                    Statement.RETURN_GENERATED_KEYS
            );
            PreparedStatement preStatement2 = connection.prepareStatement(
                    "INSERT INTO news_extrainfo ( id, source, release_time )" +
                            " VALUES ( ?, ?, ? )"
            );
            int updatedNewsCount = 0, ignoredNewsCount = 0;
            for (NewsInfoGetter.News news : newsList) {
                preStatement.setString(1, news.getTitle());
                preStatement.setString(2, news.getCategory());
                preStatement.setTimestamp(3, news.getCollect_time());
                int r = preStatement.executeUpdate();
                if (r != 0) {
                    System.out.println("[已更新 1 条] 类别：" + news.getCategory() + " 标题：" + news.getTitle());
                    NewsExtraInfoGetter.NewsExtraInfo newsExtraInfo = NewsExtraInfoGetter.getNewsExtraInfo(news);
                    ResultSet rs = preStatement.getGeneratedKeys();
                    if (newsExtraInfo != null && rs.next()) {
                        preStatement2.setInt(1, rs.getInt(1));
                        preStatement2.setString(2, newsExtraInfo.getSource());
                        preStatement2.setTimestamp(3, newsExtraInfo.getRelease_time());
                        preStatement2.executeUpdate();
                    }
                    updatedNewsCount++;
                } else {
                    System.out.println("[已存在，未更新] 类别：" + news.getCategory() + " 标题：" + news.getTitle());
                    ignoredNewsCount++;
                }

            }
            System.out.println("已更新 " + updatedNewsCount + " 条新闻，" + ignoredNewsCount + " 条重复新闻未更新");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}